package orm

import (
	"database/sql"
	"errors"
	"encoding/json"
	"bytes"
	_ "github.com/lib/pq"

	"reflect"
	"strconv"
)

var postgresOperators = map[string]string{
	"exact":       "= ?",
	"iexact":      "= UPPER(?)",
	"contains":    "LIKE ?",
	"icontains":   "LIKE UPPER(?)",
	"gt":          "> ?",
	"gte":         ">= ?",
	"lt":          "< ?",
	"lte":         "<= ?",
	"eq":          "= ?",
	"ne":          "!= ?",
	"startswith":  "LIKE ?",
	"endswith":    "LIKE ?",
	"istartswith": "LIKE UPPER(?)",
	"iendswith":   "LIKE UPPER(?)",
}

// postgresql column field types.
var postgresTypes = map[string]string{
	"bool":            "bool",
	"string":          "character varying",
	"string-char":     "char(%d)",
	"string-text":     "text",
	"time.Time-date":  "date",
	"time.Time":       "timestamp with time zone",
	"int":				"integer",
	"int8":            `smallint CHECK("%COL%" >= -127 AND "%COL%" <= 128)`,
	"int16":           "smallint",
	"int32":           "integer",
	"int64":           "bigint",
	"uint8":           `smallint CHECK("%COL%" >= 0 AND "%COL%" <= 255)`,
	"uint16":          `integer CHECK("%COL%" >= 0)`,
	"uint32":          `bigint CHECK("%COL%" >= 0)`,
	"uint64":          `bigint CHECK("%COL%" >= 0)`,
	"float64":         "double precision",
	"float64-decimal": "numeric(%d, %d)",
	"json":            "json",
	"jsonb":           "jsonb",
}

var defaultType = map[string]bool{
	"serial" : true,
}

type OrmDbPostgres struct {
	Db *sql.DB
}

func NewDb(config string)(OrmDb,error){
	if config==""{
		return nil,errors.New("db config is empty")
	}

	var cf map[string]string
	if err:=json.Unmarshal([]byte(config),&cf);err!=nil{
		return nil,err
	}

	var buf bytes.Buffer

	if _,ok:=cf["host"];!ok{
		return nil,errors.New("db init fail:host is empty")
	}
	buf.WriteString("host="+cf["host"]+" ")

	if _,ok:=cf["port"];!ok{
		return nil,errors.New("db init fail:port is empty")
	}
	buf.WriteString("port="+cf["port"]+" ")

	if _,ok:=cf["user"];!ok{
		return nil,errors.New("db init fail:user is empty")
	}
	buf.WriteString("user="+cf["user"]+" ")

	if _,ok:=cf["password"];!ok{
		return nil,errors.New("db init fail:password is empty")
	}
	buf.WriteString("password="+cf["password"]+" ")

	if _,ok:=cf["dbname"];!ok{
		return nil,errors.New("db init fail:dbname is empty")
	}
	buf.WriteString("dbname="+cf["dbname"]+" ")
	buf.WriteString("sslmode=disable")

	db,err:=sql.Open("postgres",buf.String())
	if err!=nil{
		return nil,err
	}

	err = db.Ping()
	if err!=nil{
		err = errors.New("db.Ping fail:"+err.Error())
		db.Close()
		return nil,err
	}

	psql := &OrmDbPostgres{
		Db:db,
	}

	return psql,nil
}

func init(){
	Orm.RegisterDb("postgres",NewDb)
}

func (db *OrmDbPostgres)Read(){

}

func (db *OrmDbPostgres)Insert(models ...interface{})(err error){

	if models==nil || len(models)<=0{
		return
	}

	for _,model := range models{
		typ := reflect.TypeOf(model)
		val := reflect.ValueOf(model).Elem()
		if typ.Kind() != reflect.Ptr{
			return errors.New("must provide model pointer")
		}

		modelName := typ.Elem().Name()
		modelInfo,ok := Orm.ModelCache[modelName]
		if !ok{
			return errors.New("no this model :"+modelName)
		}

		insertSql ,err:= db.getInsertSql(modelInfo)
		if err!=nil{
			return err
		}
		stmt,err :=db.Db.Prepare(insertSql)
		if err!=nil{
			return err
		}
		defer stmt.Close()

		var values []interface{}
		for _,row := range modelInfo.Columns{
			if _,ok := defaultType[row.ColumnType];ok{
				continue
			}
			values = append(values,val.FieldByName(row.ColumnName).Interface())
		}

		_,err = stmt.Exec(values...)
		if err!=nil{
			return err
		}
	}
	return nil
}

func (db *OrmDbPostgres)InsertRet(model interface{})(err error){
	return nil
}

func (db *OrmDbPostgres)Select(sql string,qs *querySet,args ...interface{})(err error){
	stmt,err := db.Db.Prepare(sql)
	if err!=nil {
		return err
	}
	defer stmt.Close()

	rows,err:= stmt.Query(args...)
	if err!=nil{
		return err
	}

	//temporary storage value


	//select *
	if len(qs.SelectColumns) == 0{
		refs := make([]interface{},len(qs.ModelInfo.Columns))
		for i := range refs {
			var ref interface{}
			refs[i] = &ref
		}

		//temp value get from scan
		for rows.Next(){
			if err:=rows.Scan(refs...);err!=nil{
				return err
			}
			if err:=db.setReceiverValuesAll(qs,refs);err!=nil{
				return err
			}
		}

		return
	}

	for rows.Next(){
		refs := make([]interface{},len(qs.SelectColumns))
		for i := range refs {
			var ref interface{}
			refs[i] = &ref
		}

		if err:=rows.Scan(refs...);err!=nil{
			return err
		}
		if err:=db.setReceiverValuesAssignCol(qs,refs);err!=nil{
			return err
		}
	}
	return nil
}

func (db *OrmDbPostgres)Update(){

}

func (db *OrmDbPostgres)Delete(sql string,args ...interface{})error{
	stmt,err := db.Db.Prepare(sql)
	if err!=nil {
		return err
	}
	defer stmt.Close()

	_,err = stmt.Exec(args...)
	if err!=nil{
		return err
	}

	return nil
}

func (db *OrmDbPostgres)CreateTable(model *ModelInfo)error{
	var buf bytes.Buffer

	buf.WriteString("CREATE TABLE IF NOT EXISTS "+model.TableName)
	buf.WriteString("( ")
	count := len(model.Columns)-1
	for i,row := range model.Columns{
		buf.WriteString(row.ColumnName+" ")
		buf.WriteString(row.ColumnType+" ")
		buf.WriteString(row.Constraints+" ")

		if i!=count{
			buf.WriteString(",")
		}
	}
	buf.WriteString(")")

	smt,err := db.Db.Prepare(buf.String())
	if err!=nil{
		return err
	}
	defer smt.Close()

	if _,err :=smt.Exec();err!=nil{
		return err
	}

	return nil
}

func (db *OrmDbPostgres)Query(){

}

func (db *OrmDbPostgres)GetDb()(*sql.DB){
	return db.Db
}

func (db *OrmDbPostgres)GetDataType()(*map[string]string){
	return &postgresTypes
}

func (db *OrmDbPostgres)getInsertSql(modelInfo *ModelInfo)(string,error){
	if modelInfo == nil{
		return "",errors.New("modelInfo is nil")
	}
	var buf bytes.Buffer

	buf.WriteString("INSERT INTO ")
	buf.WriteString(modelInfo.TableName)
	buf.WriteString("(")
	count := len(modelInfo.Columns)-1
	for i,row := range modelInfo.Columns{
		if _,ok := defaultType[row.ColumnType];ok{
			continue
		}
		buf.WriteString(row.ColumnName)
		if i!=count{
			buf.WriteString(",")
		}
	}
	buf.WriteString(")  VALUES (")

	i:=0
	for _,row := range modelInfo.Columns{
		if _,ok := defaultType[row.ColumnType];ok{
			continue
		}
		buf.WriteString("$"+strconv.Itoa(i+1))
		i++
		if i!=count{
			buf.WriteString(",")
		}
	}
	buf.WriteString(")")
	return buf.String(),nil
}

//no use
func (db *OrmDbPostgres)getTypeValue(v *reflect.Value) (interface{},error){
	switch v.Kind() {
	case reflect.Int,reflect.Int8,reflect.Int16,reflect.Int32,reflect.Int64,
			reflect.Uint,reflect.Uint8,reflect.Uint16,reflect.Uint32,reflect.Uint64:
		return v.Int(),nil
	case reflect.Float32,reflect.Float64,reflect.Complex64,reflect.Complex128:
		return v.Float(),nil
	case reflect.String:
		return v.String(),nil
	default:
		return nil,errors.New("get type's value fail")
	}
}

//set values to afferent parameter
func (db *OrmDbPostgres)setReceiverValuesAll(qs *querySet,refs []interface{})error{

	//get receiver values slice
	refGetVal := reflect.ValueOf(qs.GetValue).Elem()
	if refGetVal.Kind()!=reflect.Slice{
		return errors.New("receiver is not a slice")
	}

	//new modelInfo PtrTo(typ) value
	elm := reflect.New(qs.ModelInfo.RefVal.Elem().Type())
	mind := reflect.Indirect(elm)

	//set value to Ptr(typ) from refs
	for i,col := range qs.ModelInfo.Columns{

		refInd := reflect.Indirect(reflect.ValueOf(refs[i]))
		refIndElem := refInd.Elem()

		var assertVal interface{}
		switch refIndElem.Kind() {
		case reflect.Int,reflect.Int8,reflect.Int16,reflect.Int32,reflect.Int64,
			reflect.Uint,reflect.Uint8,reflect.Uint16,reflect.Uint32,reflect.Uint64:
			switch mind.FieldByName(col.ColumnName).Kind(){
			case reflect.Int:
				assertVal = int(refIndElem.Int())
			case reflect.Int8:
				assertVal = int8(refIndElem.Int())
			case reflect.Int16:
				assertVal = int16(refIndElem.Int())
			case reflect.Int32:
				assertVal = int32(refIndElem.Int())
			case reflect.Int64:
				assertVal = int64(refIndElem.Int())
			}

		case reflect.Float32,reflect.Float64,reflect.Complex64,reflect.Complex128:
			switch mind.FieldByName(col.ColumnName).Kind() {
			case reflect.Float32:
				assertVal = float32(refIndElem.Float())
			case reflect.Float64:
				assertVal = float64(refIndElem.Float())
			}

		case reflect.String:
			assertVal = refIndElem.String()
		default:
			return errors.New("get type's value fail")
		}
		mind.FieldByName(col.ColumnName).Set(reflect.ValueOf(assertVal))
	}

	//append to slice
	refGetVal.Set(reflect.Append(refGetVal,mind))
	return nil
}

//set values to afferent parameter
func (db *OrmDbPostgres)setReceiverValuesAssignCol(qs *querySet,refs []interface{})error{

	//get receiver values slice
	refGetVal := reflect.ValueOf(qs.GetValue).Elem()
	if refGetVal.Kind()!=reflect.Slice{
		return errors.New("receiver is not a slice")
	}

	//new modelInfo PtrTo(typ) value
	elm := reflect.New(qs.ModelInfo.RefVal.Elem().Type())
	mind := reflect.Indirect(elm)

	//set value to Ptr(typ) from refs
	for i,col := range qs.SelectColumns{

		refInd := reflect.Indirect(reflect.ValueOf(refs[i]))
		refIndElem := refInd.Elem()

		var assertVal interface{}
		switch refIndElem.Kind() {
		case reflect.Int,reflect.Int8,reflect.Int16,reflect.Int32,reflect.Int64,
			reflect.Uint,reflect.Uint8,reflect.Uint16,reflect.Uint32,reflect.Uint64:
			switch mind.FieldByName(col).Kind(){
			case reflect.Int:
				assertVal = int(refIndElem.Int())
			case reflect.Int8:
				assertVal = int8(refIndElem.Int())
			case reflect.Int16:
				assertVal = int16(refIndElem.Int())
			case reflect.Int32:
				assertVal = int32(refIndElem.Int())
			case reflect.Int64:
				assertVal = int64(refIndElem.Int())
			}

		case reflect.Float32,reflect.Float64,reflect.Complex64,reflect.Complex128:
			switch mind.FieldByName(col).Kind() {
			case reflect.Float32:
				assertVal = float32(refIndElem.Float())
			case reflect.Float64:
				assertVal = float64(refIndElem.Float())
			}

		case reflect.String:
			assertVal = refIndElem.String()
		default:
			return errors.New("get type's value fail")
		}
		mind.FieldByName(col).Set(reflect.ValueOf(assertVal))
	}
	//append to slice
	refGetVal.Set(reflect.Append(refGetVal,mind))
	return nil
}
